﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using StateGrid.MDBservice;
using System.IO;
using iTextSharp.text.pdf;

namespace StateGrid.XWLW
{
    class XWLW_MDB : MDBservice.MDBservice
    {
        public override String id
        {
            get
            {
                return "xueweilunwen";
            }
        }

        private XWLW_DAO xwlwDAO = new XWLW_DAO();

        public override void clear()
        {
            base.clear();
        }

        public override String getTest()
        {
            IList<XWLW_FL> r = xwlwDAO.GetAllXWLW_FL();
            String ret = String.Empty;
            foreach (XWLW_FL i in r)
            {
                ret += i.ToString();
            }
            return ret;
        }

        public XWLW_MDB()
        {
            pmTableName = "XWLW_WF";
            formatPmTableName = "XWLW";
            title = "学位论文";
        }

        public override void setColumnList()
        {
            this.ColumnList = new List<Column>();
            if (hasFormatTable)
            {
                this.ColumnList.Add(new Column("分类编码", OleDbType.WChar, 50));
                this.ColumnList.Add(new Column("索引编码", OleDbType.WChar, 50));
                this.ColumnList.Add(new Column("文件名", OleDbType.WChar, 50));
                this.ColumnList.Add(new Column("中文题名", OleDbType.WChar, 255));
                this.ColumnList.Add(new Column("英文题名", OleDbType.WChar, 255));
                this.ColumnList.Add(new Column("作者", OleDbType.WChar, 50));
                this.ColumnList.Add(new Column("导师", OleDbType.WChar, 50));
                this.ColumnList.Add(new Column("论文级别", OleDbType.WChar, 50));
                this.ColumnList.Add(new Column("学科专业名称", OleDbType.WChar, 150));
                this.ColumnList.Add(new Column("分类号", OleDbType.WChar, 50));
                this.ColumnList.Add(new Column("学位授予单位", OleDbType.WChar, 250));
                this.ColumnList.Add(new Column("论文提交日期", OleDbType.Date));
                this.ColumnList.Add(new Column("论文答辩日期", OleDbType.Date));
                this.ColumnList.Add(new Column("中文关键词", OleDbType.WChar, 255));
                this.ColumnList.Add(new Column("中文摘要", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("分类体系", OleDbType.WChar, 50));
                this.ColumnList.Add(new Column("英文摘要", OleDbType.WChar, 0));
                this.ColumnList.Add(new Column("年份", OleDbType.WChar, 4));
                this.ColumnList.Add(new Column("英文关键词", OleDbType.WChar, 255));
                this.ColumnList.Add(new Column("原文件名", OleDbType.WChar, 50));
                this.ColumnList.Add(new Column("出版日期", OleDbType.Date));
                this.ColumnList.Add(new Column("页数", OleDbType.WChar, 10));
                this.ColumnList.Add(new Column("学位授予日期", OleDbType.Date));
                this.ColumnList.Add(new Column("更新日期", OleDbType.Date));
                this.ColumnList.Add(new Column("全文", OleDbType.WChar, 50));
            }
            else
            {
                //未支持
            }
        }

        protected override PM getPM(OleDbDataReader reader, out String err)
        {
            err = String.Empty;
            XWLW_PM pm = new XWLW_PM();
            if (hasFormatTable)
            {
                pm.flbm = reader["分类编码"].ToString().Trim();
                pm.sybm = reader["索引编码"].ToString().Trim();
                pm.wjm = reader["文件名"].ToString().Trim();
                pm.ywjm = reader["原文件名"].ToString().Trim();
                pm.zwtm = reader["中文题名"].ToString().Trim();
                pm.ywtm = reader["英文题名"].ToString().Trim();
                pm.zz = reader["作者"].ToString().Trim();
                pm.ds = reader["导师"].ToString().Trim();
                pm.lwjb = reader["论文级别"].ToString().Trim();
                pm.xkzymc = reader["学科专业名称"].ToString().Trim();
                pm.flh = reader["分类号"].ToString().Trim();
                pm.xwsydw = reader["学位授予单位"].ToString().Trim();
                try
                {
                    pm.ys = int.Parse(reader["页数"].ToString().Trim());
                }
                catch (Exception)
                {
                    pm.ys = 0;
                }
                try
                {
                    int index = pm.ywjm.LastIndexOf('/');
                    pm.nf = int.Parse(pm.ywjm.Substring(index + 1,4));
                }
                catch (Exception)
                {
                    pm.nf = 0;
                }
                try
                {
                    if (pm.nf == 0)
                        pm.nf = int.Parse(reader["年份"].ToString().Trim());
                }
                catch (Exception)
                {
                    pm.nf = 0;
                    err += pm.sybm + ":" + pm.zwtm + ": 年份\"" + reader["年份"] + "\"出错，置为0\n";
                }
                try
                {
                    if (String.Empty.Equals(reader["论文提交日期"].ToString().Trim()))
                    {
                        //err += pm.flbm + "-" + pm.nf + ":" + pm.zwtm + ":论文提交日期为空\n";
                        pm.lwtjrq = DateTime.Parse(pm.nf.ToString() + "/1/1");
                    }
                    else
                        pm.lwtjrq = DateTime.Parse(reader["论文提交日期"].ToString().Trim());
                }
                catch
                {
                    err += pm.flbm + "-" + pm.nf + ":" + pm.zwtm + ": 论文提交日期\"" + reader["论文提交日期"] + "\"出错\n";
                }
                try
                {
                    if (String.Empty.Equals(reader["论文答辩日期"].ToString().Trim()))
                    {
                        //err += pm.flbm + "-" + pm.nf + ":" + pm.zwtm + ":论文答辩日期为空\n";
                        pm.lwdbrq = DateTime.Parse(pm.nf.ToString() + "/1/1");
                    }
                    else
                        pm.lwdbrq = DateTime.Parse(reader["论文答辩日期"].ToString().Trim());
                }
                catch
                {
                    err += pm.flbm + "-" + pm.nf + ":" + pm.zwtm + ": 论文答辩日期\"" + reader["论文答辩日期"] + "\"出错\n";
                }
                pm.zwztc = reader["中文关键词"].ToString().Trim();
                pm.zwzy = reader["中文摘要"].ToString().Trim();
                pm.fltx = reader["分类体系"].ToString().Trim();
                pm.ywzy = reader["英文摘要"].ToString().Trim();
                pm.ywztc = reader["英文关键词"].ToString().Trim();
                pm.ywjm = reader["原文件名"].ToString().Trim();
                try
                {
                    if (String.Empty.Equals(reader["出版日期"].ToString().Trim()))
                    {
                        //err += pm.flbm + "-" + pm.nf + ":" + pm.zwtm + ":出版日期为空\n";
                        pm.cbrq = DateTime.Parse(pm.nf.ToString() + "/1/1");
                    }
                    else
                        pm.cbrq = DateTime.Parse(reader["出版日期"].ToString().Trim());
                }
                catch
                {
                    err += pm.flbm + "-" + pm.nf + ":" + pm.zwtm + ": 出版日期\"" + reader["出版日期"] + "\"出错\n";
                }
                try
                {
                    if (String.Empty.Equals(reader["学位授予日期"].ToString().Trim()))
                    {
                        //err += pm.flbm + "-" + pm.nf + ":" + pm.zwtm + ":学位授予日期为空\n";
                        pm.xwsyrq = DateTime.Parse(pm.nf.ToString() + "/1/1");
                    }
                    else
                        pm.xwsyrq = DateTime.Parse(reader["学位授予日期"].ToString().Trim());
                }
                catch
                {
                    err += pm.flbm + "-" + pm.nf + ":" + pm.zwtm + ": 学位授予日期\"" + reader["学位授予日期"] + "\"出错\n";
                }
                //页数和更新日期忽略，自动填写
                pm.qw = reader["全文"].ToString().Trim();
            }
            else
            {
                //不支持
            }
            pm.dlr = user.name;
            pm.fillEmptyStringAsNull();
            return pm;
        }

        public override bool checkPMValid(PM pm, out string errMsg)
        {
            errMsg = String.Empty;
            XWLW_PM xwlw = (XWLW_PM)pm;
            this.openOleDbConnection();
            sqlcmd.CommandText = @"select * from " + formatPmTableName + " where 分类编码=@分类编码 and 年份=@年份 and 中文题名=@中文题名 and 作者=@作者 and 导师=@导师";
            sqlcmd.Connection = this.ObjConnection;
            sqlcmd.Parameters.Add(new OleDbParameter("分类编码", xwlw.flbm));
            sqlcmd.Parameters.Add(new OleDbParameter("年份", xwlw.nf));
            sqlcmd.Parameters.Add(new OleDbParameter("中文题名", xwlw.zwtm));
            sqlcmd.Parameters.Add(new OleDbParameter("作者", xwlw.zz));
            sqlcmd.Parameters.Add(new OleDbParameter("导师", xwlw.ds));
            OleDbDataReader reader = sqlcmd.ExecuteReader();

            if (reader.Read())
            {
                reader.Close();
                sqlcmd.Parameters.Clear();
                return true;
            }
            else
            {
                reader.Close();
                sqlcmd.Parameters.Clear();
                return false;
            }
            //            this.closeOleDbConnection();
        }

        public override string formatData(PM pm)
        {
            //反馈信息
            String result = String.Empty;
            String Empty = "NULL";
            XWLW_PM xwlw = (XWLW_PM)pm;

            xwlw.ds = GlobalMethod.Semicolon(xwlw.ds);
            xwlw.zwztc = GlobalMethod.Semicolon(xwlw.zwztc);
            xwlw.ywztc = GlobalMethod.Semicolon(xwlw.ywztc);
            xwlw.wjm = "自动生成";
            //xwlw.qw = "-1";
            xwlw.dlr = user.name;
            xwlw.gxrq = DateTime.Now;
            if (!xwlw.flbm.Equals("XW" + xwlw.fltx))
            {
                result += "分类编码和分类体系不匹配";
            }
            else if (xwlwDAO.GetXWLW_FLByFlbm(xwlw.flbm) == null)
            {
                result += "分类编码" + xwlw.flbm + "不存在 ";
            }
            xwlw.sybm = xwlw.flbm + "-" + xwlw.nf.ToString();
            if (Empty.Equals(xwlw.ywjm))
            {
                result += "原文件名（PDF全文）为空 ";
            }
            else
            {
                if (!File.Exists(fileFolder + xwlw.ywjm))
                {
                    result += xwlw.ywjm + " PDF文件不存在";
                }
                else if (xwlw.ys == 0)
                {
                    PdfReader pdf = new PdfReader(fileFolder + xwlw.ywjm);
                    if (null != pdf)
                    {
                        xwlw.ys = pdf.NumberOfPages;
                    }
                    else
                    {
                        xwlw.ys = 0;
                    }
                    pdf.Close();
                }
            }
            if (Empty.Equals(xwlw.zwtm) && Empty.Equals(xwlw.ywtm))
            {
                result += "中文题名和英文题名都为空 ";
            }
            if (xwlw.nf == 0)
            {
                result += "年份为空 ";
            }

            if (!result.Equals(String.Empty))
            {
                result += " : " + xwlw.nf + "年" + xwlw.zz + "《" + xwlw.zwtm + xwlw.ywtm + "》\n";
            }

            return result;
        }

        public override String insertPM(PM pm)
        {
            XWLW_PM xwlw = (XWLW_PM)pm;
            int back;
            String err = String.Empty;
            //打开连接
            this.openOleDbConnection();
            OleDbCommand sqlcmd = new OleDbCommand();
            if (!String.Empty.Equals(validateTableName(formatPmTableName)))
            {
                sqlcmd.CommandText = @"create Table " + formatPmTableName + @"(分类编码 char(50), 索引编码 char(50), 文件名 char(30), 中文题名 char(255), 英文题名 char(255), 作者 char(50), 导师 char(50), 论文级别 char(50), 学科专业名称 char(150), 分类号 char(50), 学位授予单位 char(250), 论文提交日期 datetime, 论文答辩日期 datetime, 中文关键词 char(255), 中文摘要 text, 分类体系 char(50), 英文摘要 text, 年份 char(4), 英文关键词 char(255), 原文件名 char(50), 出版日期 datetime, 页数 char(10), 学位授予日期 datetime, 更新日期 datetime, 全文 char(50))";
                sqlcmd.Connection = this.ObjConnection;
                try
                {
                    back = sqlcmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    err = e.Message + "\n";
                    return err;
                }
            }

            sqlcmd.CommandText = @"insert into " + formatPmTableName + @"(分类编码,索引编码,文件名,中文题名,英文题名,作者,导师,论文级别,学科专业名称,分类号,学位授予单位,论文提交日期,论文答辩日期,中文关键词,中文摘要,分类体系,英文摘要,年份,英文关键词,原文件名,出版日期,页数,学位授予日期,更新日期,全文) values(@分类编码,@索引编码,@文件名,@中文题名,@英文题名,@作者,@导师,@论文级别,@学科专业名称,@分类号,@学位授予单位,@学位提交日期,@学位答辩日期,@中文关键词,@中文摘要,@分类体系,@英文摘要,@年份,@英文关键词,@原文件名,@出版日期,@页数,@学位授予日期,@更新日期,@全文)";
            sqlcmd.Connection = this.ObjConnection;
            OleDbParameter parameter;
            sqlcmd.Parameters.Add(new OleDbParameter("分类编码", xwlw.flbm));
            sqlcmd.Parameters.Add(new OleDbParameter("索引编码", xwlw.sybm));
            sqlcmd.Parameters.Add(new OleDbParameter("文件名", xwlw.wjm));
            sqlcmd.Parameters.Add(new OleDbParameter("中文题名", xwlw.zwtm));
            sqlcmd.Parameters.Add(new OleDbParameter("英文题名", xwlw.ywtm));
            sqlcmd.Parameters.Add(new OleDbParameter("作者", xwlw.zz));
            sqlcmd.Parameters.Add(new OleDbParameter("导师", xwlw.ds));
            sqlcmd.Parameters.Add(new OleDbParameter("论文级别", xwlw.lwjb));
            sqlcmd.Parameters.Add(new OleDbParameter("学科专业名称", xwlw.xkzymc));
            sqlcmd.Parameters.Add(new OleDbParameter("分类号", xwlw.flh));
            sqlcmd.Parameters.Add(new OleDbParameter("学位授予单位", xwlw.xwsydw));
            parameter = new OleDbParameter("论文提交日期", OleDbType.DBDate);
            parameter.Value = xwlw.lwtjrq;
            sqlcmd.Parameters.Add(parameter);
            parameter = new OleDbParameter("论文答辩日期", OleDbType.DBDate);
            parameter.Value = xwlw.lwdbrq;
            sqlcmd.Parameters.Add(parameter);
            sqlcmd.Parameters.Add(new OleDbParameter("中文关键词", xwlw.zwztc));
            sqlcmd.Parameters.Add(new OleDbParameter("中文摘要", xwlw.zwzy));
            sqlcmd.Parameters.Add(new OleDbParameter("分类体系", xwlw.fltx));
            sqlcmd.Parameters.Add(new OleDbParameter("英文摘要", xwlw.ywzy));
            sqlcmd.Parameters.Add(new OleDbParameter("年份", xwlw.nf));
            sqlcmd.Parameters.Add(new OleDbParameter("英文关键词", xwlw.ywztc));
            sqlcmd.Parameters.Add(new OleDbParameter("原文件名", xwlw.ywjm));
            sqlcmd.Parameters.Add(new OleDbParameter("出版日期", xwlw.cbrq));
            parameter = new OleDbParameter("出版日期", OleDbType.DBDate);
            parameter.Value = xwlw.cbrq;
            sqlcmd.Parameters.Add(parameter);
            sqlcmd.Parameters.Add(new OleDbParameter("页数", xwlw.ys));
            parameter = new OleDbParameter("学位授予日期", OleDbType.DBDate);
            parameter.Value = xwlw.xwsyrq;
            sqlcmd.Parameters.Add(parameter);
            parameter = new OleDbParameter("更新日期", OleDbType.DBDate);
            parameter.Value = xwlw.gxrq;
            sqlcmd.Parameters.Add(parameter);
            sqlcmd.Parameters.Add(new OleDbParameter("全文", xwlw.qw));
            try
            {
                back = sqlcmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                err = e.Message + "\n";
            }
            sqlcmd.Parameters.Clear();

            this.closeOleDbConnection();
            return err;
        }

        public override int deletePM(PM pm, out String err)
        {
            int back = 0;
            err = String.Empty;
            XWLW_PM XWLW = (XWLW_PM)pm;
            //打开连接
            this.openOleDbConnection();
            OleDbCommand sqlcmd = new OleDbCommand();
            if (!String.Empty.Equals(validateTableName(formatPmTableName)))
            {
                sqlcmd.CommandText = @"create Table " + formatPmTableName + @"(分类编码 char(50), 索引编码 char(50), 文件名 char(30), 中文题名 char(255), 英文题名 char(255), 作者 char(50), 导师 char(50), 论文级别 char(50), 学科专业名称 char(150), 分类号 char(50), 学位授予单位 char(250), 论文提交日期 datetime, 论文答辩日期 datetime, 中文关键词 char(255), 中文摘要 text, 分类体系 char(50), 英文摘要 text, 年份 char(4), 英文关键词 char(255), 原文件名 char(50), 出版日期 datetime, 页数 char(10), 学位授予日期 datetime, 更新日期 datetime, 全文 char(50))";
                sqlcmd.Connection = this.ObjConnection;
                try
                {
                    back = sqlcmd.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    err = e.Message + "\n";
                    return 0;
                }
            }
            //sql语句
            sqlcmd.CommandText = @"delete from " + formatPmTableName + " where 索引编码=@索引编码 and 年份=@年份 and 中文题名=@中文题名 and 作者=@作者 and 导师=@导师";
            sqlcmd.Connection = this.ObjConnection;
            sqlcmd.Parameters.Add(new OleDbParameter("索引编码", XWLW.sybm));
            sqlcmd.Parameters.Add(new OleDbParameter("年份", XWLW.nf));
            sqlcmd.Parameters.Add(new OleDbParameter("中文题名", XWLW.zwtm));
            sqlcmd.Parameters.Add(new OleDbParameter("作者", XWLW.zz));
            sqlcmd.Parameters.Add(new OleDbParameter("导师", XWLW.ds));

            try
            {
                back = sqlcmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                err = "Exception: " + e.Message + "\n";
            }
            sqlcmd.Parameters.Clear();
            //            this.closeOleDbConnection();
            return back;
        }
        /* 现在的方法先delete再insert，就不用为每文库写update了
        public override bool updatePM(PM pm)
        {
            ZWQK_PM zwqk = (ZWQK_PM)pm;
            //打开连接
            this.openOleDbConnection();

            //sql语句
            OleDbCommand sqlcmd = new OleDbCommand(@"update ZWQK set 作者=@作者,作者单位=@作者单位,中文主题词=@中文主题词,外文主题词=@外文主题词,国图分类=@国图分类 where 中文篇名=@中文篇名 and 文件名=@文件名", this.ObjConnection);

            sqlcmd.Parameters.Add(new OleDbParameter("作者", zwqk.zz));
            sqlcmd.Parameters.Add(new OleDbParameter("作者单位", zwqk.zzdw));
            sqlcmd.Parameters.Add(new OleDbParameter("中文主题词", zwqk.zwztc));
            sqlcmd.Parameters.Add(new OleDbParameter("外文主题词", zwqk.wwztc));
            sqlcmd.Parameters.Add(new OleDbParameter("国图分类", zwqk.gtfl));
            sqlcmd.Parameters.Add(new OleDbParameter("中文篇名", zwqk.zwpm));
            sqlcmd.Parameters.Add(new OleDbParameter("文件名", zwqk.wjm));
            int back = sqlcmd.ExecuteNonQuery();
            if (back == 0)
            {
                return false;
            }
            //objConnection.Close();
            return true;
        }*/

        public override bool findServerDuplicate(PM pm)
        {
            XWLW_PM xwlw = (XWLW_PM)pm;
            IList<XWLW_PM> result = xwlwDAO.QueryXWLW_PM(xwlw.zwtm, xwlw.zz, xwlw.ds);
            if (result != null && result.Count != 0)
                return true;
            else
                return false;
        }

        public override String insertPMwithUpdateWJM(PM pm)
        {
            String err = String.Empty;
            XWLW_PM xwlw = (XWLW_PM)pm;
            int index;
            index = xwlw.ywjm.LastIndexOf('/');
            int serial;
            String pc = "01"; //批次怎么算？
            if ("ml".Equals(xwlw.ywjm.Substring(index + 1, 2)))
            {
                serial = 0;
            }
            else
            {

                XWLW_PM maxPm = xwlwDAO.getMaxXWLW_PM(xwlw.flbm, pc);
                if (maxPm != null)
                {
                    int pos = maxPm.wjm.LastIndexOf('-');
                    int pos2 = maxPm.wjm.LastIndexOf('.');
                    serial = int.Parse(maxPm.wjm.Substring(pos + 1, 5)) + 1;
                }
                else
                {
                    serial = 1;
                }
            }
            index = xwlw.ywjm.LastIndexOf('.');
            String suffixStr;
            if (index >= 0)
                suffixStr = xwlw.ywjm.Substring(index);
            else
                suffixStr = String.Empty;
            String serialStr = "0000" + serial.ToString();
            xwlw.wjm = xwlw.sybm + "-" + pc + "-" + serialStr.Substring(serialStr.Length - 5) + suffixStr;
            try
            {
               xwlwDAO.InsertXWLW_PM(xwlw);
            }
            catch (Exception e)
            {
                err += e.Message + "\n";
            }
            return err;
        }

        public override string addOneRecord(PM pm)
        {
            String err = String.Empty;
            XWLW_PM xwlw = (XWLW_PM)pm;
            xwlw.gxrq = DateTime.Now;
            xwlw.eraseNullInEmptyString();
            err += insertPMwithUpdateWJM(pm);
            if (!String.Empty.Equals(err))
                return err;
            String path = "";
            path += xwlw.sybm.Substring(0, 2) + "/";
            path += xwlw.sybm.Substring(2, 2) + "/";
            path += xwlw.nf.ToString() + "/";
//            path += pc + "/";

            String srcFile = fileFolder + xwlw.ywjm;
            String destFile = path + xwlw.wjm;
            try
            {
                FTPtransfer.uploadAndCreateDir(destFile, srcFile);
                System.IO.File.Delete(srcFile);
            }
            catch (Exception e)
            {
                err += "Exception: " + e.Message + ",FTP文件传输失败\n";
                xwlwDAO.DeleteXWLW_PM(xwlw.wjm, user.name);
            }
            return err;
        }

    }
}
